-- Connection Settings.
\set HIVE_SERVER         `echo \'"$HIVE_SERVER"\'`
\set HIVE_CLIENT_TYPE    `echo \'"$CLIENT_TYPE"\'`
\set HIVE_PORT           `echo \'"$HIVE_PORT"\'`
\set HIVE_USER           `echo \'"$HIVE_USER"\'`
\set HIVE_PASSWORD       `echo \'"$HIVE_PASSWORD"\'`
\set AUTH_TYPE           `echo \'"$AUTH_TYPE"\'`

\c contrib_regression
SET hdfs_fdw.enable_order_by_pushdown TO ON;
CREATE EXTENSION hdfs_fdw;

-- Create Hadoop FDW Server. log_remote_sql 'true' is required to setup logging
-- for Remote SQL Sent to Hive Server.
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER hdfs_fdw
  OPTIONS(host :HIVE_SERVER, port :HIVE_PORT, client_type :HIVE_CLIENT_TYPE, log_remote_sql 'true', auth_type :AUTH_TYPE);
CREATE SERVER hdfs_server1 FOREIGN DATA WRAPPER hdfs_fdw
  OPTIONS(host :HIVE_SERVER, port :HIVE_PORT, client_type :HIVE_CLIENT_TYPE, log_remote_sql 'true', auth_type :AUTH_TYPE);

-- Create Hadoop USER MAPPING.
CREATE USER MAPPING FOR public SERVER hdfs_server
  OPTIONS (username :HIVE_USER, password :HIVE_PASSWORD);
CREATE USER MAPPING FOR public SERVER hdfs_server1
  OPTIONS (username :HIVE_USER, password :HIVE_PASSWORD);

-- Create Foreign Table
CREATE FOREIGN TABLE dept (
    deptno          INTEGER,
    dname           VARCHAR(14),
    loc             VARCHAR(13)
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'dept');

CREATE FOREIGN TABLE dept_1 (
    deptno          INTEGER,
    dname           VARCHAR(14),
    loc             VARCHAR(13)
)
SERVER hdfs_server1 OPTIONS (dbname 'fdw_db', table_name 'dept');


CREATE FOREIGN TABLE emp (
    empno           INTEGER,
    ename           VARCHAR(10),
    job             VARCHAR(9),
    mgr             INTEGER,
    hiredate        pg_catalog.DATE,
    sal             INTEGER,
    comm            INTEGER,
    deptno          INTEGER
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'emp');

CREATE FOREIGN TABLE jobhist
(
    empno           INTEGER,
    startdate       pg_catalog.DATE,
    enddate         pg_catalog.DATE,
    job             VARCHAR(9),
    sal             FLOAT,
    comm            FLOAT,
    deptno          INTEGER,
    chgdesc         VARCHAR(80)
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'jobhist');

CREATE FOREIGN TABLE test1
(
	c1			INTEGER,
	c2			INTEGER,
	c3			VARCHAR(10)
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test1');

CREATE FOREIGN TABLE test2
(
	c1			INTEGER,
	c2			INTEGER,
	c3			VARCHAR(10)
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test2');

CREATE FOREIGN TABLE test3
(
	c1			INTEGER,
	c2			INTEGER,
	c3			VARCHAR(10)
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test3');

CREATE FOREIGN TABLE test4
(
	c1			INTEGER,
	c2			INTEGER,
	c3			VARCHAR(10)
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test4');

-- Create a local table for further testing.
CREATE TABLE local_dept (
    deptno          INTEGER,
    dname           VARCHAR(14),
    loc             VARCHAR(13)
);
INSERT INTO local_dept VALUES (10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS');

-- FDW-563: Test ORDER BY with user defined operators. Create the operator
-- family required for the test.
CREATE OPERATOR PUBLIC.<^ (
  LEFTARG = INT4,
  RIGHTARG = INT4,
  PROCEDURE = INT4EQ
);

CREATE OPERATOR PUBLIC.=^ (
  LEFTARG = INT4,
  RIGHTARG = INT4,
  PROCEDURE = INT4LT
);

CREATE OPERATOR PUBLIC.>^ (
  LEFTARG = INT4,
  RIGHTARG = INT4,
  PROCEDURE = INT4GT
);

CREATE OPERATOR FAMILY my_op_family USING btree;

CREATE FUNCTION MY_OP_CMP(A INT, B INT) RETURNS INT AS
  $$ BEGIN RETURN BTINT4CMP(A, B); END $$ LANGUAGE PLPGSQL;

CREATE OPERATOR CLASS my_op_class FOR TYPE INT USING btree FAMILY my_op_family AS
 OPERATOR 1 PUBLIC.<^,
 OPERATOR 3 PUBLIC.=^,
 OPERATOR 5 PUBLIC.>^,
 FUNCTION 1 my_op_cmp(INT, INT);

-- Disable ORDER BY push down
SET hdfs_fdw.enable_order_by_pushdown TO OFF;
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Enable ORDER BY push down
SET hdfs_fdw.enable_order_by_pushdown TO ON;
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Test plain ORDER BY
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno;
SELECT empno, mgr
  FROM emp
  ORDER BY empno;

-- Test explicit NULLS LAST
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno NULLS LAST;
SELECT empno, mgr
  FROM emp
  ORDER BY empno NULLS LAST;

-- Test ASC keyword
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno ASC;
SELECT empno, mgr
  FROM emp
  ORDER BY empno ASC;

-- Test NULLS LAST with ASC
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno ASC NULLS LAST;
SELECT empno, mgr
  FROM emp
  ORDER BY empno ASC NULLS LAST;

-- Test NULLS FIRST with ASC keyword
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno ASC NULLS FIRST;
SELECT empno, mgr
  FROM emp
  ORDER BY empno ASC NULLS FIRST;

-- Test DESC keyword
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno DESC;
SELECT empno, mgr
  FROM emp
  ORDER BY empno DESC;

-- Test NULLS FIRST with DESC
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno DESC NULLS FIRST;
SELECT empno, mgr
  FROM emp
  ORDER BY empno DESC NULLS FIRST;

-- Test NULLS LAST with DESC
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno DESC NULLS LAST;
SELECT empno, mgr
  FROM emp
  ORDER BY empno DESC NULLS LAST;

-- Test keywords with multiple ORDER BY columns
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, sal, deptno
  FROM emp
  ORDER BY deptno ASC NULLS LAST, sal DESC NULLS FIRST, empno DESC;
SELECT empno, sal, deptno
  FROM emp
  ORDER BY deptno ASC NULLS LAST, sal DESC NULLS FIRST, empno DESC;

-- Test expressions in ORDER BY clause
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY FLOOR(empno + mgr) NULLS FIRST;
SELECT empno, mgr
  FROM emp
  ORDER BY FLOOR(empno + mgr) NULLS FIRST;

EXPLAIN (COSTS FALSE, VERBOSE)
SELECT empno, mgr
  FROM emp
  ORDER BY empno + mgr;
SELECT empno, mgr
  FROM emp
  ORDER BY empno + mgr;

-- Simple aggregates
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(empno), avg(empno), min(sal), max(empno), sum(empno) * (random() <= 1)::int AS sum2 FROM emp WHERE sal > 1000 GROUP BY sal ORDER BY 1, 2;

-- GROUP BY clause HAVING expressions
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal+20, sum(sal) * (sal+20) FROM emp GROUP BY sal+20 ORDER BY sal+20;

-- Aggregates in subquery are pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(x.a), sum(x.a) FROM (SELECT sal a, sum(sal) b FROM emp GROUP BY sal ORDER BY 1, 2) x;

-- Aggregate is still pushed down by taking unshippable expression out
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal * (random() <= 1)::int AS sum1, sum(empno) * sal AS sum2 FROM emp GROUP BY sal ORDER BY 1, 2;

-- Aggregate with unshippable GROUP BY clause are not pushed
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal * (random() <= 1)::int AS c2 FROM emp GROUP BY sal * (random() <= 1)::int ORDER BY 1;

-- GROUP BY clause in various forms, cardinal, alias and constant expression
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(sal) w, sal x, 5 y, 7.0 z FROM emp GROUP BY 2, y, 9.0::int ORDER BY 2;

-- Testing HAVING clause shippability
SET enable_sort TO ON;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, sum(empno) FROM emp GROUP BY sal HAVING avg(empno) > 500 and sum(empno) > 4980 ORDER BY sal;

-- Using expressions in HAVING clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ename, count(empno) FROM emp GROUP BY ename HAVING sqrt(max(empno)) = sqrt(7900) ORDER BY 1, 2;

SET enable_sort TO off;
-- Aggregate in HAVING clause is not pushable, and thus aggregation is not pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(sal) FROM emp GROUP BY empno HAVING avg(sal * (random() <= 1)::int) > 1 ORDER BY 1;

-- Testing ORDER BY, DISTINCT, FILTER, Ordered-sets and VARIADIC within aggregates
-- ORDER BY within aggregates (same column used to order) are not pushed
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(sal ORDER BY sal) FROM emp WHERE sal < 1000 GROUP BY empno ORDER BY 1;

-- ORDER BY within aggregate (different column used to order also using DESC)
-- are not pushed.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(sal ORDER BY empno desc) FROM emp WHERE empno > 5000 and sal > 1000;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(DISTINCT (t1.sal)) FROM emp t1 join dept t2 ON (t1.deptno = t2.deptno) WHERE t1.empno < 8000 or (t1.empno is null and t2.deptno < 30) GROUP BY (t2.deptno) ORDER BY 1;

-- DISTINCT, ORDER BY and FILTER within aggregate, not pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(sal%3), sum(DISTINCT sal%3 ORDER BY sal%3) filter (WHERE sal%3 < 2), empno FROM emp WHERE empno = 7654 GROUP BY empno;

-- FILTER within aggregate, not pushed
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(sal) filter (WHERE sal < 2000 and empno > 1000) FROM emp GROUP BY empno ORDER BY 1 nulls last;

-- Outer query is aggregation query
EXPLAIN (VERBOSE, COSTS OFF)
SELECT DISTINCT (SELECT count(*) filter (WHERE t2.deptno = 20 and t1.empno < 8000) FROM emp t1 WHERE t1.empno = 7654) FROM dept t2 ORDER BY 1;

-- Inner query is aggregation query
EXPLAIN (VERBOSE, COSTS OFF)
SELECT DISTINCT (SELECT count(t1.empno) filter (WHERE t2.deptno = 20) FROM emp t1 WHERE t1.empno = 7654) FROM dept t2 ORDER BY 1;

-- Ordered-sets within aggregate, not pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, rank('10'::varchar) within group (ORDER BY ename), percentile_cont(sal/5000::numeric) within group (ORDER BY empno) FROM emp GROUP BY sal HAVING percentile_cont(sal/200::numeric) within group (ORDER BY empno) < 8000 ORDER BY sal;

-- Using multiple arguments within aggregates
EXPLAIN (VERBOSE, COSTS OFF)
SELECT empno, rank(empno, sal) within group (ORDER BY empno, sal) FROM emp GROUP BY empno, sal HAVING empno = 7654 ORDER BY 1;

-- Subquery in FROM clause HAVING aggregate
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*), x.b FROM emp, (SELECT deptno a, sum(deptno) b FROM dept GROUP BY deptno) x WHERE emp.deptno = x.a GROUP BY x.b ORDER BY 1, 2;

-- Join with IS NULL check in HAVING
EXPLAIN (VERBOSE, COSTS OFF)
SELECT avg(t1.empno), sum(t2.deptno) FROM emp t1 join dept t2 ON (t1.deptno = t2.deptno) GROUP BY t2.deptno HAVING (avg(t1.empno) is null and sum(t2.deptno) > 10) or sum(t2.deptno) is null ORDER BY 1 nulls last, 2;

-- ORDER BY expression is part of the target list but not pushed down to
-- foreign server.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sum(sal) * (random() <= 1)::int AS sum FROM emp ORDER BY 1;

-- Check with placeHolderVars
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.b, count(emp.deptno), sum(q.a) FROM emp left join (SELECT min(13), avg(emp.deptno), sum(dept.deptno) FROM emp right join dept ON (emp.deptno = dept.deptno) WHERE emp.deptno = 20) q(a, b, c) ON (emp.deptno = q.b) WHERE emp.deptno between 10 and 30 GROUP BY q.b ORDER BY 1 nulls last, 2;

-- Not supported cases
-- Grouping sets
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, sum(empno) FROM emp WHERE sal > 1000 GROUP BY rollup(sal) ORDER BY 1 nulls last;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, sum(empno) FROM emp WHERE sal > 1000 GROUP BY cube(sal) ORDER BY 1 nulls last;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, ename, sum(empno) FROM emp WHERE sal > 1000 GROUP BY grouping sets(sal, ename) ORDER BY 1 nulls last, 2 nulls last;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, sum(empno), grouping(sal) FROM emp WHERE sal > 1000 GROUP BY sal ORDER BY 1 nulls last;

-- DISTINCT itself is not pushed down, whereas underneath aggregate is pushed
EXPLAIN (VERBOSE, COSTS OFF)
SELECT DISTINCT sum(empno) s FROM emp WHERE sal > 1000 GROUP BY sal ORDER BY 1;

-- WindowAgg
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, sum(sal), count(sal) over (partition by sal) FROM emp WHERE sal > 1000 GROUP BY sal ORDER BY 1;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, array_agg(sal) over (partition by sal ORDER BY sal desc) FROM emp WHERE sal > 1000 GROUP BY sal ORDER BY 1;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, array_agg(sal) over (partition by sal ORDER BY sal range between current row and unbounded following) FROM emp WHERE sal > 1000 GROUP BY sal ORDER BY 1;

-- User defined function for user defined aggregate, VARIADIC
CREATE FUNCTION least_accum(anyelement, variadic anyarray)
returns anyelement language sql AS
  'SELECT least($1, min($2[i])) FROM generate_subscripts($2,2) g(i)';
CREATE aggregate least_agg(variadic items anyarray) (
  stype = anyelement, sfunc = least_accum
);
-- Not pushed down due to user defined aggregate
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sal, least_agg(empno) FROM emp GROUP BY sal ORDER BY sal;

-- Test partition-wise aggregates
SET enable_partitionwise_aggregate TO on;

-- Create the partition tables.
CREATE TABLE fprt1 (c1 int, c2 int, c3 varchar) PARTITION BY RANGE(c1);
CREATE FOREIGN TABLE ftprt1_p1 PARTITION OF fprt1 FOR VALUES FROM (1) TO (4)
  SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test1');
CREATE FOREIGN TABLE ftprt1_p2 PARTITION OF fprt1 FOR VALUES FROM (5) TO (8)
  SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test2');

CREATE TABLE fprt2 (c1 int, c2 int, c3 varchar) PARTITION BY RANGE(c2);
CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (1) TO (4)
  SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test3');
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (5) TO (8)
  SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'test4');

-- Plan with partitionwise aggregates is enabled
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1, sum(c1) FROM fprt1 GROUP BY c1 ORDER BY 2;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1, sum(c2), min(c2), count(*) FROM fprt1 GROUP BY c1 HAVING avg(c2) < 22 ORDER BY 2;

-- Check with whole-row reference
-- Should have all the columns in the target list for the given relation
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1, count(t1) FROM fprt1 t1 GROUP BY c1 HAVING avg(c2) < 22 ORDER BY 1;

-- When GROUP BY clause does not match with PARTITION KEY.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c2, avg(c1), max(c1), count(*) FROM fprt1 GROUP BY c2 HAVING sum(c1) < 700 ORDER BY 1;

SET enable_partitionwise_aggregate TO off;

-- Test ORDER BY pushdown with aggregates
-- Explain Plan with ORDER BY pushdown turned off
SET hdfs_fdw.enable_order_by_pushdown TO OFF;
explain (verbose, costs off)
select count(empno) from emp group by job order by job;

-- Explain Plan with ORDER BY pushdown turned on
SET hdfs_fdw.enable_order_by_pushdown TO ON;
explain (verbose, costs off)
select count(empno) from emp group by job order by job;

SET enable_mergejoin TO off;
SET enable_hashjoin TO off;
SET enable_sort TO off;
SET hdfs_fdw.enable_order_by_pushdown TO ON;

-- Test INNER JOIN push-down
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Test LEFT OUTER JOIN push-down
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e LEFT JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Test RIGHT OUTER JOIN push-down
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e RIGHT JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Test FULL OUTER JOIN push-down
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e FULL JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Full outer join with restrictions on the joining relations
-- a. the joining relations are both base relations
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.empno, t2.empno
  FROM (SELECT empno FROM emp WHERE empno BETWEEN 7500 AND 7800) t1 FULL JOIN
    (SELECT empno FROM jobhist WHERE empno BETWEEN 7500 AND 7800) t2
    ON (t1.empno = t2.empno)
  ORDER BY 1, 2;

-- b. one of the joining relations is a base relation and the other is a join
-- relation
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.empno, ss.a, ss.b
  FROM (SELECT empno FROM emp WHERE empno between 7500 and 7800) t1 FULL JOIN
    (SELECT t2.empno, t3.empno FROM emp t2 LEFT JOIN jobhist t3
      ON (t2.empno = t3.empno) WHERE (t2.empno between 7500 and 7800)) ss (a, b)
    ON (t1.empno = ss.a)
  ORDER BY t1.empno, ss.a, ss.b;

-- c. test deparsing the remote query as nested subqueries
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.empno, ss.a, ss.b
  FROM (SELECT empno FROM emp WHERE empno between 7500 and 7800) t1 FULL JOIN
    (SELECT t2.empno, t3.empno
      FROM (SELECT empno FROM emp WHERE empno BETWEEN 7500 AND 7800) t2 FULL JOIN
       (SELECT empno FROM jobhist WHERE empno BETWEEN 7500 AND 7800) t3
	  ON (t2.empno = t3.empno) WHERE t2.empno IS NULL OR t2.empno IS NOT NULL) ss(a, b)
  ON (t1.empno = ss.a)
  ORDER BY t1.empno, ss.a, ss.b;

-- d. test deparsing rowmarked relations as subqueries
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.deptno, ss.a, ss.b
  FROM (SELECT deptno FROM local_dept WHERE deptno = 10) t1 INNER JOIN
    (SELECT t2.empno, t3.empno
      FROM (SELECT empno FROM emp WHERE empno BETWEEN 7500 AND 7800) t2 FULL JOIN
       (SELECT empno FROM jobhist WHERE empno BETWEEN 7500 AND 7800) t3
      ON (t2.empno = t3.empno) WHERE t2.empno IS NULL OR t2.empno IS NOT NULL) ss(a, b)
  ON (TRUE)
  ORDER BY t1.deptno, ss.a, ss.b
  FOR UPDATE OF t1;

-- Test a three table join, all are foreign tables, should be pushed.
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT jh.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno) JOIN jobhist jh ON (jh.empno = e.empno)
  ORDER BY 1, 2, 3;

EXPLAIN (COSTS false, VERBOSE)
SELECT jh.empno, e.ename, d.dname
  FROM emp e, dept d, jobhist jh WHERE e.deptno = 10 AND d.deptno = 20 AND jh.deptno = 30
  ORDER BY 1, 2, 3 LIMIT 10;

-- Clauses within the nullable side are not pulled up, but the top level clause
-- on nullable side is not pushed down into nullable side
EXPLAIN (COSTS false, VERBOSE)
SELECT e.empno, e.ename, d.dname, d.loc
  FROM emp e LEFT JOIN (SELECT * FROM dept WHERE deptno > 20) d ON (e.deptno = d.deptno)
  WHERE (d.deptno < 50 OR d.deptno IS NULL) AND e.deptno > 20
  ORDER BY 1, 2, 3, 4;

-- Test multiple joins in a single statement
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.ename, jh.empno, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno) RIGHT JOIN jobhist jh ON (e.empno = jh.empno)
  ORDER BY 1, 2, 3;

-- Join two tables with FOR UPDATE clause
EXPLAIN (COSTS false, VERBOSE)
SELECT e.empno, d.deptno
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY 1, 2 FOR UPDATE OF e;

EXPLAIN (COSTS false, VERBOSE)
SELECT e.empno, d.deptno
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY 1, 2 FOR UPDATE;

-- Test join between a table and a resultset of a subquery.
EXPLAIN (COSTS false, VERBOSE)
SELECT e.empno, e.ename, d.dname, d.loc
  FROM emp e LEFT JOIN (SELECT * FROM dept WHERE deptno > 20) d ON (e.deptno = d.deptno)
  WHERE e.deptno > 20
  ORDER BY 1, 2, 3, 4;

-- Join with CTE
EXPLAIN (COSTS false, VERBOSE)
WITH t (c1, c2, c3) AS (
  SELECT e.empno, e.ename, d.deptno
    FROM emp e JOIN dept d ON (e.deptno = d.deptno)
) SELECT c1, c3 FROM t ORDER BY 1, 2;

-- Test JOIN between a foreign table and a local table, should not be pushed
-- down.
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN local_dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Test whole-row reference
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e, d
  FROM emp e FULL JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e, d, e.ename
  FROM emp e FULL JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- SEMI JOIN, not pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT e.empno
  FROM emp e WHERE EXISTS (SELECT 1 FROM dept d WHERE e.deptno = d.deptno)
  ORDER BY 1;

-- ANTI JOIN, not pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT e.empno
  FROM emp e WHERE NOT EXISTS (SELECT 1 FROM dept d WHERE e.deptno = d.deptno)
  ORDER BY 1;

-- CROSS JOIN can be pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT e.empno, d.deptno
  FROM emp e CROSS JOIN dept d
  ORDER BY 1, 2 LIMIT 10;

-- CROSS JOIN of two foreign tables again cross joined with a local table.
-- CROSS JOIN of foreign tables should be pushed down, and local table should
-- be joined locally.
EXPLAIN (COSTS false, VERBOSE)
SELECT e.ename, d.deptno, ld.dname
  FROM emp e CROSS JOIN dept d CROSS JOIN local_dept ld
  ORDER BY 1, 2, 3 LIMIT 10;

-- Join two tables from two different foreign servers
EXPLAIN (COSTS false, VERBOSE)
SELECT e.deptno, d.deptno
  FROM emp e JOIN dept_1 d ON (e.deptno = d.deptno)
  ORDER BY 1, 2;

-- Check join pushdown in situations where multiple userids are involved
CREATE ROLE regress_view_owner SUPERUSER;
CREATE USER MAPPING FOR regress_view_owner SERVER hdfs_server
  OPTIONS (username :HIVE_USER, password :HIVE_PASSWORD);
GRANT SELECT ON emp TO regress_view_owner;
GRANT SELECT ON dept TO regress_view_owner;
CREATE VIEW v1 AS SELECT * FROM emp;
CREATE VIEW v2 AS SELECT * FROM dept;
ALTER VIEW v2 OWNER TO regress_view_owner;

-- Can't be pushed down, different view owners
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.deptno, t2.deptno
  FROM v1 t1 LEFT JOIN v2 t2 ON (t1.deptno = t2.deptno)
  ORDER BY t1.deptno, t2.deptno LIMIT 10;

-- Same user for the views, can be pushed down.
ALTER VIEW v1 OWNER TO regress_view_owner;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.deptno, t2.deptno
  FROM v1 t1 LEFT JOIN v2 t2 ON (t1.deptno = t2.deptno)
  ORDER BY t1.deptno, t2.deptno LIMIT 10;

-- Can't be pushed down, view owner not current user
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.deptno, t2.deptno
  FROM v1 t1 LEFT JOIN dept t2 ON (t1.deptno = t2.deptno)
  ORDER BY t1.deptno, t2.deptno LIMIT 10;

ALTER VIEW v1 OWNER TO CURRENT_USER;
-- Can be pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.deptno, t2.deptno
  FROM v1 t1 LEFT JOIN dept t2 ON (t1.deptno = t2.deptno)
  ORDER BY t1.deptno, t2.deptno LIMIT 10;
ALTER VIEW v1 OWNER TO regress_view_owner;

-- Non-Var items in targetlist of the nullable rel of a join preventing
-- push-down in some cases
-- Unable to push {emp, dept}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.a, dept.deptno
  FROM (SELECT 20 FROM emp WHERE deptno = 20) q(a) RIGHT JOIN dept ON (q.a = dept.deptno)
  WHERE dept.deptno BETWEEN 10 AND 30
  ORDER BY 1, 2;

-- Ok to push {emp, dept} but not {emp, dept, jobhist}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT jobhist.deptno, q.*
  FROM jobhist LEFT JOIN
    (SELECT 13, emp.deptno, dept.deptno
       FROM emp RIGHT JOIN dept ON (emp.deptno = dept.deptno)
       WHERE emp.deptno = 10) q(a, b, c)
  ON (jobhist.deptno = q.b)
  WHERE jobhist.deptno BETWEEN 10 AND 20
  ORDER BY 1, 2;

-- Test partition-wise join
SET enable_partitionwise_join TO on;

-- Inner join three tables
-- Different explain plan on v10 as partition-wise join is not supported there.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1,t2.c2,t3.c3
  FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.c1 = t2.c2) INNER JOIN fprt1 t3 ON (t2.c2 = t3.c1)
  WHERE t1.c1 % 2 =0 ORDER BY 1,2,3;

-- With whole-row reference; partitionwise join does not apply
-- Table alias in foreign scan is different for v12, v11 and v10.
EXPLAIN (VERBOSE, COSTS false)
SELECT t1, t2, t1.c1
  FROM fprt1 t1 JOIN fprt2 t2 ON (t1.c1 = t2.c2)
  ORDER BY t1.c3, t1.c1;

-- Join with lateral reference
-- Different explain plan on v10 as partition-wise join is not supported there.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1,t1.c2
  FROM fprt1 t1, LATERAL (SELECT t2.c1, t2.c2 FROM fprt2 t2
  WHERE t1.c1 = t2.c2 AND t1.c2 = t2.c1) q WHERE t1.c1 % 2 = 0 ORDER BY 1,2;

-- With PHVs, partitionwise join selected but no join pushdown
-- Table alias in foreign scan is different for v12, v11 and v10.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t1.phv, t2.c2, t2.phv
  FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE c1 % 2 = 0) t1 LEFT JOIN
    (SELECT 't2_phv' phv, * FROM fprt2 WHERE c2 % 2 = 0) t2 ON (t1.c1 = t2.c2)
  ORDER BY t1.c1, t2.c2;

SET enable_partitionwise_join TO off;

EXPLAIN (COSTS FALSE, VERBOSE)
SELECT t1.c1, t2.c1
  FROM test1 t1 JOIN test2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1;

-- FDW-563: Test ORDER BY with user defined operators. User defined operators
-- are not pushed down.
SET hdfs_fdw.enable_order_by_pushdown TO ON;

EXPLAIN (COSTS FALSE, VERBOSE)
SELECT * FROM dept ORDER BY deptno USING OPERATOR(public.<^);

EXPLAIN (COSTS FALSE, VERBOSE)
SELECT min(e.empno)
  FROM emp e GROUP BY e.deptno
  ORDER BY e.deptno USING OPERATOR(public.<^);

-- FDW-556: Support enable_order_by_pushdown option at server level and table
-- level.
-- Check only boolean values are accepted.
ALTER SERVER hdfs_server OPTIONS (ADD enable_order_by_pushdown 'abc11');

-- Test default behavior of table and server level settings
-- By default order by pushdown is enabled at table and server level,
-- order by is pushed down since it's also enabled at GUC level.
SET hdfs_fdw.enable_order_by_pushdown TO ON;
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename FROM emp e ORDER BY e.empno;

-- Disable order by pushdown at GUC level and test the options at table
-- and server level.
SET hdfs_fdw.enable_order_by_pushdown TO OFF;

-- Test the option at server level.
-- No pushdown since it's disabled at GUC and server level.
ALTER SERVER hdfs_server OPTIONS (ADD enable_order_by_pushdown 'false');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename FROM emp e ORDER BY e.empno;

-- No pushdown since it's disabled at GUC level.
ALTER SERVER hdfs_server OPTIONS (SET enable_order_by_pushdown 'true');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename FROM emp e ORDER BY e.empno;

-- Test the option at table level. No pushdown since it's disabled at GUC
-- and table level.
ALTER FOREIGN TABLE emp OPTIONS (ADD enable_order_by_pushdown 'false');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename FROM emp e ORDER BY e.empno;

-- No pushdown since it's disabled at GUC level.
ALTER FOREIGN TABLE emp OPTIONS (SET enable_order_by_pushdown 'true');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename FROM emp e ORDER BY e.empno;

-- Enable order by pushdown at GUC level.
SET hdfs_fdw.enable_order_by_pushdown TO ON;
SHOW hdfs_fdw.enable_order_by_pushdown;

-- Table level overrides server level option, here pushdown is disabled at
-- server level and enabled at table level order by pushed down since pushdown
-- is enabled at GUC and table level.
ALTER SERVER hdfs_server OPTIONS (SET enable_order_by_pushdown 'false');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename FROM emp e ORDER BY e.empno;

-- Table level overrides server level option, here pushdown is enabled
-- at server level and disabled at table level, order by not pushed down since
-- pushdown is disabled at table level.
ALTER SERVER hdfs_server OPTIONS (SET enable_order_by_pushdown 'true');
ALTER FOREIGN TABLE emp OPTIONS (SET enable_order_by_pushdown 'false');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename FROM emp e ORDER BY e.empno;

-- Test table and server level settings with a simple join, pushdown is
-- disabled for emp and enabled for dept, order by is not pushed down.
ALTER FOREIGN TABLE dept OPTIONS (SET enable_order_by_pushdown 'true');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Test table and server level settings with a simple join, pushdown is enabled
-- for emp and dept, order by is pushed down.
ALTER FOREIGN TABLE emp OPTIONS (SET enable_order_by_pushdown 'true');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  ORDER BY e.empno;

-- Test table and server level settings with complex join, order by is not
-- pushed down since at least one of the tables has pushdown disabled.
ALTER FOREIGN TABLE jobhist OPTIONS (ADD enable_order_by_pushdown 'false');
EXPLAIN (COSTS FALSE, VERBOSE)
SELECT e.empno, e.ename, d.dname
  FROM emp e JOIN dept d ON (e.deptno = d.deptno) JOIN jobhist h ON (d.deptno = h.deptno)
  ORDER BY e.empno;

-- Cleanup
SET hdfs_fdw.enable_order_by_pushdown TO OFF;
DROP TABLE local_dept;
DROP OWNED BY regress_view_owner;
DROP ROLE regress_view_owner;
DROP aggregate least_agg(variadic items anyarray);
DROP FUNCTION least_accum(anyelement, variadic anyarray);
DROP FOREIGN TABLE emp;
DROP FOREIGN TABLE dept;
DROP FOREIGN TABLE jobhist;
DROP FOREIGN TABLE dept_1;
DROP FOREIGN TABLE ftprt1_p1;
DROP FOREIGN TABLE ftprt1_p2;
DROP TABLE IF EXISTS fprt1;
DROP TABLE IF EXISTS fprt2;
DROP FOREIGN TABLE test1;
DROP FOREIGN TABLE test2;
DROP FOREIGN TABLE test3;
DROP FOREIGN TABLE test4;
DROP OPERATOR CLASS my_op_class USING btree;
DROP FUNCTION my_op_cmp(a INT, b INT);
DROP OPERATOR FAMILY my_op_family USING btree;
DROP OPERATOR public.>^(INT, INT);
DROP OPERATOR public.=^(INT, INT);
DROP OPERATOR public.<^(INT, INT);
DROP USER MAPPING FOR public SERVER hdfs_server;
DROP USER MAPPING FOR public SERVER hdfs_server1;
DROP SERVER hdfs_server;
DROP SERVER hdfs_server1;
DROP EXTENSION hdfs_fdw;
